package com.oocl.jdbc_homework.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.oocl.jdbc_homework.dao.BookDao;
import com.oocl.jdbc_homework.pojo.Book;
import com.oocl.jdbc_homework.util.DBUtil;

public class BookDaoImpl implements BookDao {

	@Override
	public int addBook(Book book) {
		String sql = "insert into book(id, name, pub_date, author, price, is_new, publisher) values(seq02.nextval,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			pst.setString(1, book.getName());
			pst.setDate(2, new java.sql.Date(book.getPubDate().getTime()));
			pst.setString(3, book.getAuthor());
			pst.setDouble(4, book.getPrice());
			pst.setBoolean(5, book.isUnused());
			pst.setString(6, book.getPublisher());
			
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
			System.out.println((m == 1) ? "插入成功" : "插入失败");
			return m;
		}
	}

	@Override
	public int deleteBook(Integer id) {
		String sql = "delete from book where id = ?";
		PreparedStatement pst = null;
		Connection conn = null;
		int m = 0;
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
			System.out.println((m == 1) ? "删除成功" : "删除失败");
			return m;
		}
	}

	@Override
	public int updateBook(Book book) {
		String sql = "update book set name=?, pub_date=?, author=?, price=?, is_new=?, publisher=? where id=?";
		Connection conn = null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			pst.setString(1, book.getName());
			pst.setDate(2, new java.sql.Date(book.getPubDate().getTime()));
			pst.setString(3, book.getAuthor());
			pst.setDouble(4, book.getPrice());
			pst.setBoolean(5, book.isUnused());
			pst.setString(6, book.getPublisher());
			pst.setInt(7, book.getId());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
			System.out.println((m == 1) ? "修改成功" : "修改失败");
			return m;
		}
	}

	@Override
	public Book searchBook(Integer id) {
		String sql = "select * from book where id=?";
		PreparedStatement pst = null;
		ResultSet set = null;
		Connection conn = null;
		Book book = null;
		try {
			conn = DBUtil.createConnection();
			// conn = ConPool.getInstance().getCon();
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			set = pst.executeQuery();
			while (set.next()) {
				int cid = set.getInt(1);
				String cname = set.getString(2);
				System.out.println("id = " + cid + ", cname = " + cname);

				book = new Book(set.getInt("id"), set.getString("name"),
						new Date(set.getDate("pub_date").getTime()),
						set.getString("author"), set.getDouble("price"),
						set.getBoolean("is_new"), set.getString("publisher"));
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, set);
			return book;
		}
	}

	@Override
	public List<Book> findAllBooks() {
		String sql = "select * from book";
		PreparedStatement pst = null;
		ResultSet set = null;
		Connection conn = null;
		List<Book> list = new ArrayList<Book>();
		try {
			conn = DBUtil.createConnection();
			// conn = ConPool.getInstance().getCon();
			pst = conn.prepareStatement(sql);
			set = pst.executeQuery();
			while (set.next()) {
				int id = set.getInt(1);
				String cname = set.getString(2);
				System.out.println("id = " + id + ", cname = " + cname);

				Book book = new Book(set.getInt("id"), set.getString("name"),
						new Date(set.getDate("pub_date").getTime()),
						set.getString("author"), set.getDouble("price"),
						set.getBoolean("is_new"), set.getString("publisher"));
				list.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, set);
			return list;
		}
	}

	@Override
	public int addBooks(List<Book> book) {
		String sql = "insert into book(id, name, pub_date, author, price, is_new, publisher) values(seq02.nextval,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement pst = null;
		int m = 1;
		try {
			conn = DBUtil.createConnection();
			pst = conn.prepareStatement(sql);
			int i=0;
			for(Book item: book){
				if(i==100){
					int[] res = pst.executeBatch();
					conn.commit();
					for(int it:res)
						if(it==0)
							m=0;
					i=0;
				}
				
				pst.setString(1, item.getName());
				pst.setDate(2, new java.sql.Date(item.getPubDate().getTime()));
				pst.setString(3, item.getAuthor());
				pst.setDouble(4, item.getPrice());
				pst.setBoolean(5, item.isUnused());
				pst.setString(6, item.getPublisher());
				pst.addBatch();
				i++;
			}
			int[] res = pst.executeBatch();
			conn.commit();
			if(res!=null && res.length>0)
				for(int it :res)
					if(it ==0)
						m=0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
			System.out.println((m == 1) ? "插入成功" : "插入失败");
			return m;
		}
	}

}
